Hello，大家好~~

我們今天要延續昨天練習的aggregate function 並繼續完成情境3、4

經過一天的沉澱後，好像有試出解法了，雖然都好醜 XD

情境3：所有user completed 的todos 平均完成率

SELECT ROUND(COUNT(CASE WHEN completed = true then 1 END)::NUMERIC / COUNT(completed), 2) AS completed_rate
FROM todos

如此就能順利解出情境3 了

情境4：各個user 各自completed 的todos 完成率

SELECT "userId", ROUND(COUNT(CASE WHEN completed = true then 1 END)::NUMERIC / COUNT(completed), 2) AS completed_rate
FROM todos
GROUP BY "userId"

接著讓我們試著用subquery 寫出情境4：

SELECT "userId", completed_rate
FROM (
		SELECT "userId", ROUND((COUNT(CASE WHEN completed = true THEN 1 END)::NUMERIC / COUNT(completed)), 2) AS completed_rate
		FROM todos 
		GROUP BY "userId") as completed_sql

那我們又有沒有辦法用Active Record 的方法寫出同樣結果而不用raw_sql 呢？

情境3：所有user completed 的todos 平均率

irb(main):014:0> ((Todo.where(completed: true).count) / (Todo.count).to_f).round(2)
   (0.8ms)  SELECT COUNT(*) FROM "todos" WHERE "todos"."completed" = $1  [["completed", true]]
   (0.6ms)  SELECT COUNT(*) FROM "todos"
0.45

情境4：各個user 各自completed 的todos 完成率

Todo.pluck(:userId).uniq.sort.map do |userid|
  p "#{userid}: #{(Todo.where(userId: userid, completed: true).count / Todo.where(userId: userid).count.to_f).round(2)}"
end

1: 0.52
2: 0.4
3: 0.35
4: 0.3
5: 0.6
6: 0.3
7: 0.45
8: 0.55
9: 0.4
10: 0.6

# 好醜 -_-

啊，那麼今天就先這樣吧！謝謝大家~~